﻿using System;
using System.Web.Services;
using System.Data.SqlClient;

public partial class CallBacks : PageX
{
    public SqlConnection OpenDB()
    {
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString =
            "Data Source=.\\SQLEXPRESS;" +
            "AttachDbFilename=|DataDirectory|db1.mdf;" +
            "Integrated Security=True;User Instance=True";

        try
        {
            conn.Open();
            return conn;
        }
        catch (Exception ex)
        {
            return null;
        }

    }

    // 
    // Справочник цветов
    //
    public void GetColorDictionary()
    {
        int page = Convert.ToInt32( _REQUEST["page"] );
        int limit = Convert.ToInt32(_REQUEST["rows"]);
        string sidx = _REQUEST["sidx"];
        string sord = _REQUEST["sord"];
        bool search = Convert.ToBoolean(_REQUEST["_search"]);

        string uid = (string)Session["name"];

// Проверяем данные
        limit = (limit != 0) ? limit : 1;
        sidx = (sidx != null && sidx != "") ? sidx : "color_id";
        sord = (sord == "desc" || sord == "DESC") ? sord : "ASC";

// Создаем структуру для возврата
        jqGridData r = new jqGridData();

// Фильтр
        string whereclaus = "";
        bool concat = false;
        if (search)
        {
            string scolor_id = _REQUEST["color_id"];
            if (scolor_id != null && scolor_id != "")
            {
                whereclaus += (concat ? " AND " : "") + "color_id=" + quote(scolor_id);
                concat = true;
            }
            string scolor_code = _REQUEST["color_code"];
            if (scolor_code != null && scolor_code != "")
            {
                whereclaus += (concat ? " AND " : "") + "color_code LIKE " + quote("%"+scolor_code+"%");
                concat = true;
            }
            string scolor_name = _REQUEST["color_name"];
            if (scolor_name != null && scolor_name != "")
            {
                whereclaus += (concat ? " AND " : "") + "color_name LIKE " + quote("%" + scolor_name + "%");
                concat = true;
            }
        }
        if( whereclaus != ""  ) whereclaus = " WHERE "+whereclaus;

// Открываем базу данных
        SqlConnection conn = OpenDB();
        if (conn == null)
        {
            json_response(1, "Ошибка базы данных.");
            return;
        }

        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;

// Получаем количество записей с учетом фильтра
        comm.CommandText = "SELECT COUNT(*) FROM colors"+whereclaus+";";
        Int32 count = (Int32)comm.ExecuteScalar();

// Пустая таблица
        if (count == 0)
        {
            conn.Close();
            conn.Dispose();
            echo(json_encode(r));
            return;
        }

// Вычисляем предельные значения
        int total_pages = count / limit + (count % limit != 0 ? 1 : 0);
        if (page > total_pages) page = total_pages;

// Рассчитываем стартовое значение для LIMIT запроса
        int start_row = limit*page - limit + 1;
        int end_row = start_row + limit -1;

        if (end_row > count) end_row = count;

//SELECT * FROM ( SELECT        ROW_NUMBER() OVER (ORDER BY color_name DESC)  AS row_num, *
//FROM colors WHERE color_code != '00FF00' ) as sub WHERE row_num BETWEEN  2 AND 3

        string order = "ORDER BY " + sidx + " " + sord;
        string sub = "SELECT ROW_NUMBER() OVER (" + order + ") AS RowNum, * FROM colors" + whereclaus;

        // Формируем запрос
        comm.CommandText = "SELECT * FROM (" + sub + ") as sub WHERE sub.RowNum BETWEEN " +
                        start_row + " AND " + end_row + ";";

        SqlDataReader reader = comm.ExecuteReader();

        r.page = page;
        r.total = total_pages;
        r.records = count;
        r.rows = new jqGridData.row[end_row-start_row+1];

        int icolor_id   = reader.GetOrdinal("color_id");
        int icolor_code = reader.GetOrdinal("color_code");
        int icolor_name = reader.GetOrdinal("color_name");

        int i = 0;
        while (reader.Read())
        {
            int color_id = reader.GetInt32(icolor_id);

            r.rows[i] = new jqGridData.row();
            r.rows[i].id = color_id;
            r.rows[i].cell = new string[3];
            r.rows[i].cell[0] = color_id.ToString();
            r.rows[i].cell[1] = reader.GetInt32(icolor_code).ToString();
            r.rows[i].cell[2] = reader.GetString(icolor_name);
            i++;
        }
        reader.Close();

        conn.Close();
        conn.Dispose();

        string result = json_encode(r);

//        result = "{\"page\":\"1\",\"total\":1,\"records\":\"2\",\"rows\":[" +
//        "{\"id\":\"1\",\"cell\":[\"1\",\"000000\",\"Black\"]},{\"id\":\"2\",\"cell\":[\"2\",\"FFFFFF\",\"White\"]}]}";

        Response.AddHeader("Content-type", "text/script; charset=utf-8");
        echo(result);
    }

    public void AddToColorDictionary()
    {
        string color_id = _REQUEST["id"];
        string color_code = _REQUEST["color_code"];
        string color_name = _REQUEST["color_name"];

        if (color_id != "_empty" || !str_IsUInt32(color_code) ||
            color_name == null || color_name.Length == 0)
        {
            json_response(1, "Нарушение формата данных");
            return;
        }

        // Открываем базу данных
        SqlConnection conn = OpenDB();
        if (conn == null)
        {
            json_response(1, "Ошибка базы данных");
            return;
        }


        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;

        while (true)
        {
            Int32 result = 0;

            // Проверяем существование
            comm.CommandText = "SELECT COUNT(*) FROM colors WHERE ( " +
                "UPPER(color_name)=" + quote(color_name.ToUpper()) + " OR " +
                "UPPER(color_code)=" + quote(color_code) + " );";

            result = (Int32)comm.ExecuteScalar();
            if (result > 0)
            {
                json_response(1, "В справочнике уже присутствует цвет с указанным кодом или названием.");
                break;
            }

            comm.CommandText = "INSERT INTO colors (color_code, color_name) VALUES (" +
                quote(color_code) + "," + quote(color_name) + ");";

            result = comm.ExecuteNonQuery();
            if (result != 1)
                json_response(1, "Ошибка добавления новой записи.");
            else
                json_response(0, "Ok");

            break;
        }

        conn.Close();
        conn.Dispose();
        return;
    }

    public void EditColorDictionary()
    {
        string color_id = _REQUEST["id"];
        string color_code = _REQUEST["color_code"];
        string color_name = _REQUEST["color_name"];

        if (!str_IsUInt32(color_id) || !str_IsUInt32(color_code) ||
            color_name == null || color_name.Length == 0)
        {
            json_response(1, "Нарушение формата данных");
            return;
        }

        // Открываем базу данных
        SqlConnection conn = OpenDB();
        if (conn == null)
        {
            json_response(1, "Ошибка базы данных");
            return;
        }


        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;

        while (true)
        {
            Int32 result = 0;

            // Проверяем сеществование
            comm.CommandText = "SELECT COUNT(*) FROM colors WHERE ( " +
                "UPPER(color_name)=" + quote(color_name.ToUpper()) + " OR " +
                "UPPER(color_code)=" + quote(color_code) + " ) AND " +
                "color_id != " + quote(color_id) + ";";
            result = (Int32)comm.ExecuteScalar();
            if (result > 0)
            {
                json_response(1, "В справочнике уже присутствует цвет с указанным кодом или названием.");
                break;
            }

            comm.CommandText = "UPDATE colors SET " +
                "color_code=" + quote(color_code) + "," +
                "color_name=" + quote(color_name) + " " +
                "WHERE color_id=" + quote(color_id) + ";";

            result = comm.ExecuteNonQuery();
            if (result != 1)
                json_response(1, "Ошибка обновления записи.");
            else
                json_response(0, "Ok");

            break;
        }

        conn.Close();
        conn.Dispose();
        return;
    }

    public void DeleteColorDictionary()
    {
        string color_id = _REQUEST["id"];
        string[] color_ids = color_id.Split(new Char[] { ',' });
        string color_id_list = "";
        string delim = "";
        Int32 result = 0;

        if (color_ids.Length == 0)
        {
            json_response(1, "Ошибка удаления записи.");
            return;
        }

        for (int i = 0; i < color_ids.Length; i++)
        {
            if (!str_IsUInt32(color_ids[i]))
            {
                json_response(1, "Нарушение формата данных");
                return;
            }
            color_id_list += delim+color_ids[i];
            if (i == 0) delim = ",";
        }
 
        // Открываем базу данных
        SqlConnection conn = OpenDB();
        if (conn == null)
        {
            json_response(1, "Ошибка базы данных");
            return;
        }


        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;

        while (true)
        {
            comm.CommandText = "SELECT COUNT(*) FROM cars WHERE color_id IN (" + color_id_list + ");";
            result = (Int32)comm.ExecuteScalar();
            if (result > 0)
            {
                json_response(1, "Один или несколько цветов уже задействованы. Поэтому их удаление невозможно.");
                break;
            }

            comm.CommandText = "DELETE FROM colors WHERE color_id IN (" + color_id_list + ");";
            result = comm.ExecuteNonQuery();
            if (result <= 0)
                json_response(1, "Ошибка удаления записи.");
            else
                json_response(0, "Ok");

            break;
        }
        conn.Close();
        conn.Dispose();
        return;
    }

    public void FindColorDictionary()
    {
        string term = _REQUEST["term"];

        string uid = (string)Session["name"];

        // Проверяем данные
        if (term == null || term == "")
        {
            json_response(1, "Нарушение формата данных");
            return;
        }

        // Открываем базу данных
        SqlConnection conn = OpenDB();
        if (conn == null)
        {
            json_response(1, "Ошибка базы данных");
            return;
        }


        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;

        string whereclaus = " WHERE color_name LIKE " + quote("%" + term + "%");

        // Получаем количество записей с учетом фильтра
        comm.CommandText = "SELECT COUNT(*) FROM colors" + whereclaus + ";";
        Int32 count = (Int32)comm.ExecuteScalar();

        // Пустая таблица
        if (count == 0)
        {
            conn.Close();
            conn.Dispose();
            json_response(0, "Ok");
            return;
        }

        // Формируем запрос
        comm.CommandText = "SELECT * FROM colors" + whereclaus + ";";

        SqlDataReader reader = comm.ExecuteReader();

        unity[] u = new unity[count];

        int icolor_id = reader.GetOrdinal("color_id");
        int icolor_name = reader.GetOrdinal("color_name");

        int i = 0;
        while (reader.Read() && i < count)
        {
            u[i].value = reader.GetInt32(icolor_id);
            u[i].name = reader.GetString(icolor_name);
            i++;
        }
        reader.Close();

        conn.Close();
        conn.Dispose();

        json_response(0, "Ok", u);
        return;
    }



    // 
    // Справочник производителей (марок)
    //
    public void GetVendorDictionary()
    {
        int page = Convert.ToInt32(_REQUEST["page"]);
        int limit = Convert.ToInt32(_REQUEST["rows"]);
        string sidx = _REQUEST["sidx"];
        string sord = _REQUEST["sord"];
        bool search = Convert.ToBoolean(_REQUEST["_search"]);

        string uid = (string)Session["name"];

        // Проверяем данные
        limit = (limit != 0) ? limit : 1;
        sidx = (sidx != null && sidx != "") ? sidx : "vend_id";
        sord = (sord == "desc" || sord == "DESC") ? sord : "ASC";

        // Создаем структуру для возврата
        jqGridData r = new jqGridData();

        // Фильтр
        string whereclaus = "";
        bool concat = false;
        if (search)
        {
            string svend_id = _REQUEST["vend_id"];
            if (svend_id != null && svend_id != "")
            {
                whereclaus += (concat ? " AND " : "") + "vend_id=" + quote(svend_id);
                concat = true;
            }
            string svend_name = _REQUEST["vend_name"];
            if (svend_name != null && svend_name != "")
            {
                whereclaus += (concat ? " AND " : "") + "vend_name LIKE " + quote("%" + svend_name + "%");
                concat = true;
            }
        }
        if (whereclaus != "") whereclaus = " WHERE " + whereclaus;

        // Открываем базу данных
        SqlConnection conn = OpenDB();
        if (conn == null)
        {
            json_response(1, "Ошибка базы данных.");
            return;
        }

        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;

        // Получаем количество записей с учетом фильтра
        comm.CommandText = "SELECT COUNT(*) FROM vendors" + whereclaus + ";";
        Int32 count = (Int32)comm.ExecuteScalar();

        // Пустая таблица
        if (count == 0)
        {
            conn.Close();
            conn.Dispose();
            echo(json_encode(r));
            return;
        }

        // Вычисляем предельные значения
        int total_pages = count / limit + (count % limit != 0 ? 1 : 0);
        if (page > total_pages) page = total_pages;

        // Рассчитываем стартовое значение для LIMIT запроса
        int start_row = limit * page - limit + 1;
        int end_row = start_row + limit - 1;

        if (end_row > count) end_row = count;

        string order = "ORDER BY " + sidx + " " + sord;
        string sub = "SELECT ROW_NUMBER() OVER (" + order + ") AS RowNum, * FROM vendors" + whereclaus;

        // Формируем запрос
        comm.CommandText = "SELECT * FROM (" + sub + ") as sub WHERE sub.RowNum BETWEEN " +
                        start_row + " AND " + end_row + ";";

        SqlDataReader reader = comm.ExecuteReader();

        r.page = page;
        r.total = total_pages;
        r.records = count;
        r.rows = new jqGridData.row[end_row - start_row + 1];

        int ivend_id = reader.GetOrdinal("vend_id");
        int ivend_name = reader.GetOrdinal("vend_name");

        int i = 0;
        while (reader.Read())
        {
            int vend_id = reader.GetInt32(ivend_id);

            r.rows[i] = new jqGridData.row();
            r.rows[i].id = vend_id;
            r.rows[i].cell = new string[2];
            r.rows[i].cell[0] = vend_id.ToString();
            r.rows[i].cell[1] = reader.GetString(ivend_name);
            i++;
        }
        reader.Close();

        conn.Close();
        conn.Dispose();

        Response.AddHeader("Content-type", "text/script; charset=utf-8");
        echo(json_encode(r));
    }

    public void AddToVendorDictionary()
    {
        string vend_id = _REQUEST["id"];
        string vend_name = _REQUEST["vend_name"];

        if (vend_id != "_empty" || vend_name == null || vend_name.Length == 0)
        {
            json_response(1, "Нарушение формата данных");
            return;
        }

        // Открываем базу данных
        SqlConnection conn = OpenDB();
        if (conn == null)
        {
            json_response(1, "Ошибка базы данных.");
            return;
        }

        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;

        while (true)
        {
            Int32 result = 0;

            // Проверяем сеществование
            comm.CommandText = "SELECT COUNT(*) FROM vendors WHERE UPPER(vend_name)=" + quote(vend_name.ToUpper()) + ";";
            result = (Int32) comm.ExecuteScalar();
            if (result > 0)
            {
                json_response(1, "Эта марка уже присутствует в справочнике");
                break;
            }

            // Добавляем
            comm.CommandText = "INSERT INTO vendors (vend_name) VALUES (" + quote(vend_name) + ");";

            result = comm.ExecuteNonQuery();
            if (result != 1)
                json_response(1, "Ошибка добавления новой записи.");
            else
                json_response(0, "Ok");

            break;
        }
        conn.Close();
        conn.Dispose();
        return;
    }

    public void EditVendorDictionary()
    {
        string vend_id = _REQUEST["id"];
        string vend_name = _REQUEST["vend_name"];

        if (!str_IsUInt32(vend_id) || vend_name == null || vend_name.Length == 0)
        {
            json_response(1, "Нарушение формата данных");
            return;
        }

        // Открываем базу данных
        SqlConnection conn = OpenDB();
        if (conn == null)
        {
            json_response(1, "Ошибка базы данных.");
            return;
        }

        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;

        while (true)
        {
            Int32 result = 0;

            // Проверяем сеществование
            comm.CommandText = "SELECT COUNT(*) FROM vendors WHERE UPPER(vend_name)=" + quote(vend_name.ToUpper()) + 
                               " AND vend_id != "+vend_id+";";
            result = (Int32)comm.ExecuteScalar();
            if (result > 0)
            {
                json_response(1, "Эта марка уже присутствует в справочнике");
                break;
            }

            // Изменяем
            comm.CommandText = "UPDATE vendors SET " +
                "vend_name=" + quote(vend_name) + " " +
                "WHERE vend_id=" + quote(vend_id) + ";";

            result = comm.ExecuteNonQuery();
            if (result != 1)
                json_response(1, "Ошибка обновления записи.");
            else
                json_response(0, "Ok");

            break;
        }

        conn.Close();
        conn.Dispose();
        return;
    }

    public void DeleteVendorDictionary()
    {
        string vend_id = _REQUEST["id"];
        string[] vend_ids = vend_id.Split(new Char[] { ',' });
        string vend_id_list = "";
        string delim = "";

        if (vend_ids.Length == 0)
        {
            json_response(1, "Ошибка удаления записи.");
            return;
        }

        for (int i = 0; i < vend_ids.Length; i++)
        {
            if (!str_IsUInt32(vend_ids[i]))
            {
                json_response(1, "Нарушение формата данных");
                return;
            }
            vend_id_list += delim + vend_ids[i];
            if (i == 0) delim = ",";
        }

        // Открываем базу данных
        SqlConnection conn = OpenDB();
        if (conn == null)
        {
            json_response(1, "Ошибка базы данных.");
            return;
        }

        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;

        try
        {
            comm.CommandText =
               "BEGIN TRAN VENDOR_DELETE;" +
               "DELETE FROM price WHERE car_id IN ( SELECT car_id FROM cars "+
                    "WHERE vend_id IN (" + vend_id_list + ") );" +
               "DELETE FROM cars WHERE vend_id IN (" + vend_id_list + ");" +
               "DELETE FROM models WHERE vend_id IN (" + vend_id_list + ");" +
               "DELETE FROM vendors WHERE vend_id IN (" + vend_id_list + ");" +
               "COMMIT TRAN VENDOR_DELETE;";
            comm.ExecuteNonQuery();
            json_response(0, "Ok");
        }
        catch (Exception ex)
        {
            comm.CommandText = "ROLLBACK TRAN VENDOR_DELETE;";
            comm.ExecuteNonQuery();
            json_response(1, "Ошибка удаления записи.");
        }

        conn.Close();
        conn.Dispose();
        return;
    }


    public void FindVendorDictionary()
    {
        string term = _REQUEST["term"];

       string uid = (string)Session["name"];

        // Проверяем данные
        if (term == null || term == "")
        {
            json_response(1,"Нарушение формата данных");
            return;
        }

        // Открываем базу данных
        SqlConnection conn = OpenDB();
        if (conn == null)
        {
            json_response(1, "Ошибка базы данных.");
            return;
        }

        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;

        string whereclaus = " WHERE vend_name LIKE "+quote("%"+term+"%");

        // Получаем количество записей с учетом фильтра
        comm.CommandText = "SELECT COUNT(*) FROM vendors" + whereclaus + ";";
        Int32 count = (Int32)comm.ExecuteScalar();

        // Пустая таблица
        if (count == 0)
        {
            conn.Close();
            conn.Dispose();
            json_response(0, "Ok");
            return;
        }

        // Формируем запрос
        comm.CommandText = "SELECT * FROM vendors" + whereclaus + ";";

        SqlDataReader reader = comm.ExecuteReader();

        unity[] u = new unity[count];

        int ivend_id = reader.GetOrdinal("vend_id");
        int ivend_name = reader.GetOrdinal("vend_name");

        int i = 0;
        while (reader.Read() && i<count)
        {
            u[i].value = reader.GetInt32(ivend_id);
            u[i].name = reader.GetString(ivend_name);
            i++;
        }
        reader.Close();

        conn.Close();
        conn.Dispose();

        json_response(0, "Ok", u);
        return;
    }


    
    // 
    // Справочник моделей
    //
    public void GetModelDictionary()
    {
        int page = Convert.ToInt32(_REQUEST["page"]);
        int limit = Convert.ToInt32(_REQUEST["rows"]);
        string sidx = _REQUEST["sidx"];
        string sord = _REQUEST["sord"];
        bool search = Convert.ToBoolean(_REQUEST["_search"]);

        string uid = (string)Session["name"];

        // Проверяем данные
        limit = (limit != 0) ? limit : 1;
        sidx = (sidx != null && sidx != "") ? sidx : "model_id";
        sord = (sord == "desc" || sord == "DESC") ? "DESC" : "ASC";

        // Создаем структуру для возврата
        jqGridData r = new jqGridData();

        // Фильтр
        string whereclaus = "";
        bool concat = false;

        string svend_id = _REQUEST["vend_id"];
        if (svend_id != null && svend_id != "")
        {
            whereclaus += (concat ? " AND " : "") + "a.vend_id=" + quote(svend_id);
            concat = true;
        }
        else
        {
            echo(json_encode(r));
            return;
        }

        if (search)
        {
            string smodel_id = _REQUEST["model_id"];
            if (smodel_id != null && smodel_id != "")
            {
                whereclaus += (concat ? " AND " : "") + "a.model_id=" + quote(smodel_id);
                concat = true;
            }
            string smodel_name = _REQUEST["model_name"];
            if (smodel_name != null && smodel_name != "")
            {
                whereclaus += (concat ? " AND " : "") + "a.model_name LIKE " + quote("%" + smodel_name + "%");
                concat = true;
            }
        }
        if (whereclaus != "") whereclaus = " WHERE " + whereclaus;

        // Открываем базу данных
        SqlConnection conn = OpenDB();
        if (conn == null)
        {
            json_response(1, "Ошибка базы данных.");
            return;
        }

        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;

        // Получаем количество записей с учетом фильтра
        comm.CommandText = "SELECT COUNT(*) FROM models AS a" + whereclaus + ";";
        Int32 count = (Int32)comm.ExecuteScalar();

        // Пустая таблица
        if (count == 0)
        {
            conn.Close();
            conn.Dispose();
            echo(json_encode(r));
            return;
        }

        // Вычисляем предельные значения
        int total_pages = count / limit + (count % limit != 0 ? 1 : 0);
        if (page > total_pages) page = total_pages;

        // Рассчитываем стартовое значение для LIMIT запроса
        int start_row = limit * page - limit + 1;
        int end_row = start_row + limit - 1;

        if (end_row > count) end_row = count;

        //SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY color_name DESC)  AS row_num, *
        //FROM colors WHERE color_code != '00FF00' ) as sub WHERE row_num BETWEEN  2 AND 3

// SELECT ROW_NUMBER() OVER (ORDER BY b.vend_name ASC) AS row_num, a.model_id, b.vend_name AS vendor, a.model_name AS model
// FROM models AS a LEFT OUTER JOIN vendors AS b ON b.vend_id = a.vend_id
// WHERE (a.vend_id = 4 AND a.model_name LIKE '%Civi%')

        sidx = ( sidx == "vend_id" ) ?  "b.vend_name" : "a."+sidx;

        string order = "ORDER BY " + sidx + " " + sord;
        string sub = "SELECT ROW_NUMBER() OVER (" + order + ") AS RowNum, a.model_id AS model_id, "+
                    "b.vend_name AS vend_name, a.model_name AS model_name FROM models AS a "+
                    "LEFT OUTER JOIN vendors AS b ON b.vend_id = a.vend_id"+ whereclaus;

        // Формируем запрос
        comm.CommandText = "SELECT * FROM (" + sub + ") as sub WHERE sub.RowNum BETWEEN " +
                        start_row + " AND " + end_row + ";";

        SqlDataReader reader = comm.ExecuteReader();

        r.page = page;
        r.total = total_pages;
        r.records = count;
        r.rows = new jqGridData.row[end_row - start_row + 1];

        int imodel_id = reader.GetOrdinal("model_id");
        int ivend_name  = reader.GetOrdinal("vend_name");
        int imodel_name = reader.GetOrdinal("model_name");

        int i = 0;
        while (reader.Read())
        {
            int model_id = reader.GetInt32(imodel_id);

            r.rows[i] = new jqGridData.row();
            r.rows[i].id = model_id;
            r.rows[i].cell = new string[3];
            r.rows[i].cell[0] = model_id.ToString();
            r.rows[i].cell[1] = reader.GetString(ivend_name);
            r.rows[i].cell[2] = reader.GetString(imodel_name);
            i++;
        }
        reader.Close();

        conn.Close();
        conn.Dispose();

        Response.AddHeader("Content-type", "text/script; charset=utf-8");
        echo(json_encode(r));
    }

    public void AddToModelDictionary()
    {
        string vend_id = _REQUEST["vend_id"];
        string model_id = _REQUEST["id"];
        string model_name = _REQUEST["model_name"];

        if (model_id != "_empty" || !str_IsUInt32(vend_id) || model_name == null || model_name.Length == 0)
        {
            json_response(1, "Нарушение формата данных");
            return;
        }

        // Открываем базу данных
        SqlConnection conn = OpenDB();
        if (conn == null)
        {
            json_response(1, "Ошибка базы данных.");
            return;
        }

        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;

        while (true)
        {
            Int32 result = 0;

            // Проверяем сеществование
            comm.CommandText = "SELECT COUNT(*) FROM models WHERE "+
                "vend_id="+quote(vend_id)+" AND "+
                "UPPER(model_name)=" + quote(model_name.ToUpper()) + ";";
            result = (Int32)comm.ExecuteScalar();
            if (result > 0)
            {
                json_response(1, "Эта марка уже присутствует в справочнике");
                break;
            }

            // Добавляем
            comm.CommandText = "INSERT INTO models (vend_id, model_name) VALUES ("+quote(vend_id)+", "+ quote(model_name)+");";

            result = comm.ExecuteNonQuery();
            if (result != 1)
                json_response(1, "Ошибка добавления новой записи.");
            else
                json_response(0, "Ok");

            break;
        }
        conn.Close();
        conn.Dispose();
        return;
    }

    public void EditModelDictionary()
    {
        string model_id = _REQUEST["id"];
        string vend_id  = _REQUEST["vend_id"];
        string model_name = _REQUEST["model_name"];

        if (!str_IsUInt32(model_id) || !str_IsUInt32(vend_id) || model_name == null || model_name.Length == 0)
        {
            json_response(1, "Нарушение формата данных");
            return;
        }

        // Открываем базу данных
        SqlConnection conn = OpenDB();
        if (conn == null)
        {
            json_response(1, "Ошибка базы данных.");
            return;
        }

        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;

        while (true)
        {
            Int32 result = 0;

            // Проверяем сеществование
            comm.CommandText = "SELECT COUNT(*) FROM models WHERE UPPER(model_name)=" + quote(model_name.ToUpper()) +
                                " AND vend_id = " + vend_id + 
                                " AND model_id != " + model_id + ";";
            result = (Int32)comm.ExecuteScalar();
            if (result > 0)
            {
                json_response(1, "Эта модель уже присутствует в справочнике");
                break;
            }

            // Изменяем
            comm.CommandText = "UPDATE models SET " +
                "model_name=" + quote(model_name) + " " +
                "WHERE model_id=" + quote(model_id) + ";";

            result = comm.ExecuteNonQuery();
            if (result != 1)
                json_response(1, "Ошибка обновления записи.");
            else
                json_response(0, "Ok");

            break;
        }

        conn.Close();
        conn.Dispose();
        return;
    }

    public void DeleteModelDictionary()
    {
        string model_id = _REQUEST["id"];
        string[] model_ids = model_id.Split(new Char[] { ',' });
        string model_id_list = "";
        string delim = "";

        if (model_ids.Length == 0)
        {
            json_response(1, "Ошибка удаления записи.");
            return;
        }

        for (int i = 0; i < model_ids.Length; i++)
        {
            if (!str_IsUInt32(model_ids[i]))
            {
                json_response(1, "Нарушение формата данных");
                return;
            }
            model_id_list += delim + model_ids[i];
            if (i == 0) delim = ",";
        }

        // Открываем базу данных
        SqlConnection conn = OpenDB();
        if (conn == null)
        {
            json_response(1, "Ошибка базы данных.");
            return;
        }

        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;
        try
        {
            comm.CommandText =
               "BEGIN TRAN MODEL_DELETE;" +
               "DELETE FROM price WHERE car_id IN ( SELECT car_id FROM cars " +
                    "WHERE model_id IN (" + model_id_list + ") );" +
               "DELETE FROM cars WHERE model_id IN (" + model_id_list + ");" +
               "DELETE FROM models WHERE model_id IN (" + model_id_list + ");" +
               "COMMIT TRAN MODEL_DELETE;";
            comm.ExecuteNonQuery();
            json_response(0, "Ok");
        }
        catch (Exception ex)
        {
            comm.CommandText = "ROLLBACK TRAN MODEL_DELETE;";
            comm.ExecuteNonQuery();
            json_response(1, "Ошибка удаления записи.");
        }

        conn.Close();
        conn.Dispose();
        return;
    }


    public void FindModelDictionary()
    {
        string term = _REQUEST["term"];
        string vend_id = _REQUEST["vend_id"];

        string uid = (string)Session["name"];

        // Проверяем данные
        if (term == null || term == "" || !str_IsUInt32(vend_id) )
        {
            json_response(1, "Нарушение формата данных");
            return;
        }

        // Открываем базу данных
        SqlConnection conn = OpenDB();
        if (conn == null)
        {
            json_response(1, "Ошибка базы данных.");
            return;
        }

        SqlCommand comm = new SqlCommand();
        comm.Connection = conn;

        string whereclaus = " WHERE vend_id="+quote(vend_id)+" AND model_name LIKE " + quote("%" + term + "%");

        // Получаем количество записей с учетом фильтра
        comm.CommandText = "SELECT COUNT(*) FROM models" + whereclaus + ";";
        Int32 count = (Int32)comm.ExecuteScalar();

        // Пустая таблица
        if (count == 0)
        {
            conn.Close();
            conn.Dispose();
            json_response(0, "Ok");
            return;
        }

        // Формируем запрос
        comm.CommandText = "SELECT * FROM models" + whereclaus + ";";

        SqlDataReader reader = comm.ExecuteReader();

        unity[] u = new unity[count];

        int imodel_id = reader.GetOrdinal("model_id");
        int imodel_name = reader.GetOrdinal("model_name");

        int i = 0;
        while (reader.Read() && i < count)
        {
            u[i].value = reader.GetInt32(imodel_id);
            u[i].name = reader.GetString(imodel_name);
            i++;
        }
        reader.Close();

        conn.Close();
        conn.Dispose();

        json_response(0, "Ok", u);
        return;
    }


} //End of class CallBacks
